﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace InterestCalculationBiz
{
   public static class LoanHandler
    {
        public static DataSet CalculateLoan(LoanProperties propObj)
        {
            /*
             Table Return 
             * LoanTable *find below*
             * InterestTable *find below*
             * DifferenceTable *find in loanmethod class in Cal_Difference_dataTable*
             * RateChange *find in loanmethod class in Cal_Difference_dataTable*
             */
            DataSet ds = new DataSet();
            DataTable _dtLoan = null;
            DataSet _dsDifference = null;
            DataTable _dtInterest = null;
            double advCharges = 0;
            advCharges = propObj.Principal + propObj.BrokerageFee + propObj.CreditSearchFee + propObj.MonthlyFee + propObj.SecurityFee+propObj.OtherCharges+propObj.EstabalishmentFee ;
            double _desiredACR = 0.48;
            if (propObj.LoanType.ToLower() == "maximuminterestrate")
            {
                if (propObj.IsUpdate)
                {
                    double Repayment=0;

                    double InterestRate = LoanMethod.Max_InterestRate(propObj.DesiredACR/100, propObj, advCharges);
                    propObj.InterestRate =Math.Round(InterestRate*100,4);
                    double _repayment = LoanMethod.Repayment(propObj.DesiredACR / 100, propObj.Frequency, propObj.LoanDuration, propObj.Principal);
                  //  double _trailrate =0;
                   //_trailrate = LoanMethod.trailRate(propObj.Frequency, propObj.InterestRate);
                   // _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100);
                   // _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate);
                    ds = do_All(propObj.DesiredACR/100, propObj, advCharges, "dd/MM/yyyy", 0,_repayment, true, true,"",0,false);
                    DataTable dtOutput = new DataTable();
                    dtOutput.Columns.Add("PresentACR");
                    dtOutput.Columns.Add("DesiredACR");
                    dtOutput.Columns.Add("MaximumInterest");
                    dtOutput.Columns.Add("RevisedRepayment");
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        if (ds.Tables["DifferenceTable"].Rows.Count > 0)
                        {
                            Repayment = Convert.ToDouble(ds.Tables["DifferenceTable"].Rows[0]["Repayment"]);
                        }
                    }
                    if (ds.Tables.Contains("InterestTable"))
                    {
                        if (ds.Tables["InterestTable"].Rows.Count > 0)
                        {
                            DataRow dr = dtOutput.NewRow();
                            try
                            {
                                dr[0] =Math.Round(Convert.ToDouble(ds.Tables["InterestTable"].Rows[0]["Comparisonrate"]),2);
                            }
                            catch (Exception) {
                                dr[0] = 0;
                            }
                            dr[1] = propObj.DesiredACR;
                            dr[2] =Math.Round(InterestRate*100,2);
                            dr[3] =Repayment;
                            dtOutput.Rows.Add(dr);
                        }
                    }
                    dtOutput.TableName = "Output";
                    ds.Merge(dtOutput);  
                }
                else
                {
                    /*do-all*/
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                    DataTable dtOutput = new DataTable();
                    dtOutput.Columns.Add("PresentACR");
                    if (ds.Tables.Contains("InterestTable"))
                    {
                        if (ds.Tables["InterestTable"].Rows.Count > 0)
                        {
                            DataRow dr = dtOutput.NewRow();
                            dr[0] = ds.Tables["InterestTable"].Rows[0]["Comparisonrate"];
                            dtOutput.Rows.Add(dr);
                        }
                    }
                    dtOutput.TableName = "Output";
                    ds.Merge(dtOutput);  
                    /**/
                }
            }
            else if (propObj.LoanType.ToLower() == "changerate")
            {
                /*update button code*/
                if (propObj.IsUpdate)
                {

                    ds = do_All(_desiredACR, propObj, advCharges);
                    double _intt_diff = 0;
                    double _balanceAsOnLastTxDate = 0;
                    int _InstallmentRemain = 0;
                    double _NoofRepaymentmade = 0;
                    double _Calbalance = 0;
                    if (ds.Tables.Contains("RateChange"))
                    {
                        for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                        {
                            if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Interest from Last Tx Date")
                            {
                                _intt_diff = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeRate"]);
                            }
                            else if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "No. of Repayments Made (M2)")
                            {
                                _NoofRepaymentmade = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeRate"]);
                            }
                        }
                    }
                    propObj.LoanDuration = propObj.NewTerm;
                    double Repayment = 0;
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        if (ds.Tables["DifferenceTable"].Rows.Count > 0)
                        {
                            Repayment = Convert.ToDouble(ds.Tables["DifferenceTable"].Rows[0]["Repayment"]);
                        }
                    }
                    double _trailrate = 0;
                    _trailrate = LoanMethod.trailRate(propObj.Frequency, propObj.InterestRate);
                    _InstallmentRemain = LoanMethod.No_of_Installments(propObj.LoanDuration, propObj.Frequency);

                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable"); 
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable"); 
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange"); 
                    }
                    ds.Merge(_dsDifference); 
                    if (ds.Tables.Contains("RateChange"))
                    {
                        for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                        {
                            if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Balance as on last Tx date.")
                            {
                                _balanceAsOnLastTxDate = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeRate"]);
                            }
                        }
                    }
                    _Calbalance = _balanceAsOnLastTxDate + _intt_diff;
                    propObj.Principal = Math.Round(_Calbalance - propObj.OtherCharges, 2);
                    advCharges = Math.Round(_Calbalance, 2);

                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);

                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference); 
                    string _advanceValue="";
                    if (ds.Tables.Contains("RateChange"))
                    {
                        for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                        {
                            if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Last Tx date before Effective Date")
                            {
                                propObj.LoanDate = Convert.ToDateTime(ds.Tables["RateChange"].Rows[i]["ChangeRate"]).ToString("dd/MM/yyyy"); ;
                            }
                        }
                    }

                    propObj.InterestRate = propObj.NewInterestRate;
                    propObj.FirstRepaymentDate = Convert.ToDateTime(propObj.NewRepaymentDate).ToString("dd/MM/yyyy");

                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference);
                    if (ds.Tables.Contains("RateChange"))
                    {
                        for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                        {
                            if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Last Tx Date is for")
                            {
                                _advanceValue = Convert.ToString(ds.Tables["RateChange"].Rows[i]["ChangeRate"]);
                            }
                        }
                    }
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0,false, true,_advanceValue);
                    if (ds.Tables.Contains("Output"))
                    {
                        if (ds.Tables["Output"].Rows.Count > 1)
                        {
                            ds.Tables["Output"].Rows[0][2] = _NoofRepaymentmade;
                        }
                    }
                }
                else {
                    /*do-all*/
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                    /**/
                }
                 /************/

            }
            else if (propObj.LoanType.ToLower() == "changeallpayment")
            {   
                 if (propObj.IsUpdate)
                {
                /*do_all*/
                ds = do_All(_desiredACR, propObj, advCharges);

                /*Code For Update Button*/
                double Repayment = 0;
                string advancevalue = "";
                double _balanceAsOnLastTxDate = 0;
                double _NoofRepaymentmade = 0;
                int _InstallmentRemain = 0;
                if (ds.Tables.Contains("RateChange"))
                {
                    for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                    {
                        if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Balance as on last Tx date.")
                        {
                            try{
                            _balanceAsOnLastTxDate = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeRepmt"]);
                            }catch(Exception){}
                        }else if(Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "No. of Repayments Made (M2)")
                        {
                            _NoofRepaymentmade=Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeRepmt"]);
                        }
                    }
                }

                if (ds.Tables.Contains("RateChange"))
                {
                    for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                    {
                        if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Last Tx date before Effective Date")
                        {
                            propObj.LoanDate = Convert.ToDateTime(ds.Tables["RateChange"].Rows[i]["ChangeRepmt"]).ToString("dd/MM/yyyy"); ;
                        }
                    }
                }
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    if (ds.Tables["DifferenceTable"].Rows.Count > 0)
                    {
                        Repayment = Convert.ToDouble(ds.Tables["DifferenceTable"].Rows[0]["Repayment"]);
                    }
                }
                double _trailrate = 0;
                _trailrate = LoanMethod.trailRate(propObj.Frequency, propObj.InterestRate);
                _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100);
                _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate);
                if (ds.Tables.Contains("LoanTable"))
                {
                    ds.Tables.Remove("LoanTable");
                    _dtLoan.TableName = "LoanTable";
                    ds.Tables.Add(_dtLoan);
                }
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    ds.Tables.Remove("DifferenceTable");
                }
                if (ds.Tables.Contains("RateChange"))
                {
                    ds.Tables.Remove("RateChange");
                }
                ds.Merge(_dsDifference);
                if (ds.Tables.Contains("RateChange"))
                {
                    for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                    {
                        if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Last Tx Date is for")
                        {
                            advancevalue = Convert.ToString(ds.Tables["RateChange"].Rows[i]["ChangeRepmt"]);
                        }
                    }
                }

                _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain, false, advancevalue);
                _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);
                if (ds.Tables.Contains("LoanTable"))
                {
                    ds.Tables.Remove("LoanTable");
                    _dtLoan.TableName = "LoanTable";
                    ds.Tables.Add(_dtLoan);
                }
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    ds.Tables.Remove("DifferenceTable");
                }
                if (ds.Tables.Contains("RateChange"))
                {
                    ds.Tables.Remove("RateChange");
                }
                ds.Merge(_dsDifference);

                propObj.Principal = _balanceAsOnLastTxDate;
                advCharges = _balanceAsOnLastTxDate;
              
                propObj.FirstRepaymentDate = Convert.ToDateTime(propObj.StartDate).ToString("dd/MM/yyyy");
                Repayment = propObj.Repayment;
                //Worksheets("calc").Range("q24") = Round(Worksheets("Calc").Range("i4") * Worksheets("Calc").Range("d6") / 365, 8)
                _trailrate = LoanMethod.trailRate(propObj.Frequency, propObj.InterestRate);
                _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain, false, advancevalue);
                _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);
                if (ds.Tables.Contains("LoanTable"))
                {
                    ds.Tables.Remove("LoanTable");
                    _dtLoan.TableName = "LoanTable";
                    ds.Tables.Add(_dtLoan);
                }
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    ds.Tables.Remove("DifferenceTable");
                }
                if (ds.Tables.Contains("RateChange"))
                {
                    ds.Tables.Remove("RateChange");
                }
                ds.Merge(_dsDifference);
                if (ds.Tables.Contains("RateChange"))
                {
                    for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                    {
                        if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "No. of Installments Remaining")
                        {
                            _InstallmentRemain = Convert.ToInt32(ds.Tables["RateChange"].Rows[i]["ChangeRepmt"]);
                        }
                    }
                }
                _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain,false,advancevalue);
                _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);
                if (ds.Tables.Contains("LoanTable"))
                {
                    ds.Tables.Remove("LoanTable");
                    _dtLoan.TableName = "LoanTable";
                    ds.Tables.Add(_dtLoan);
                }
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    ds.Tables.Remove("DifferenceTable");
                }
                if (ds.Tables.Contains("RateChange"))
                {
                    ds.Tables.Remove("RateChange");
                }
                ds.Merge(_dsDifference);
                double _balance = 0;
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    if (ds.Tables["DifferenceTable"].Rows.Count > 0)
                    {
                        _balance = Convert.ToDouble(ds.Tables["DifferenceTable"].Rows[0]["Balance"]);
                    }
                }
                //  increment = Application.WorksheetFunction.RoundUp(Worksheets("Calc").Range("Q19") / Worksheets("Calc").Range("I9"), 0)
                //Worksheets("Calc").Range("N11") = Worksheets("Calc").Range("N11") + increment 
                double increment=0;
                while (_balance > 0)
                {
                    increment=LoanMethod.ROUNDUP(_balance/Repayment,0);
                   _InstallmentRemain += (int)(_balance);
                  _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain,false,advancevalue);
                  _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _InstallmentRemain);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference);
                     _balance = 0;
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        if (ds.Tables["DifferenceTable"].Rows.Count > 0)
                        {
                            _balance = Convert.ToDouble(ds.Tables["DifferenceTable"].Rows[0]["Balance"]);
                        }
                    }
                }
                _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _InstallmentRemain, false, advancevalue);
                _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate,"dd/MM/yyyy", _InstallmentRemain,true);
                _dtLoan = SetOutputFormat_loanTable(_dtLoan, "dd/MM/yyyy");
                if (ds.Tables.Contains("LoanTable"))
                {
                    ds.Tables.Remove("LoanTable");
                    _dtLoan.TableName = "LoanTable";
                    ds.Tables.Add(_dtLoan);
                }
                if (ds.Tables.Contains("DifferenceTable"))
                {
                    ds.Tables.Remove("DifferenceTable");
                }
                if (ds.Tables.Contains("RateChange"))
                {
                    ds.Tables.Remove("RateChange");
                }
                ds.Merge(_dsDifference);
                if (ds.Tables.Contains("Output"))
                {
                    if (ds.Tables["Output"].Rows.Count > 2)
                    {
                        ds.Tables["Output"].Rows[2][2] = _NoofRepaymentmade;
                    }
                }
                }
                 else
                 {
                     /*do-all*/
                     ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                     /**/
                 }
            }
            else if (propObj.LoanType.ToLower()  == "changefrequence")
            {
                /*do_all*/
                if (propObj.IsUpdate)
                {
                    ds = do_All(_desiredACR, propObj, advCharges);
                    propObj.NewRepaymentDate = Convert.ToDateTime(propObj.NextRepaymentDue).ToString("dd/MM/yyyy");  
                    try
                    {
                        if (Convert.ToDateTime(propObj.NextRepaymentDue) <= Convert.ToDateTime(propObj.LoanDate))
                        {
                            return ds;
                        }
                    }
                    catch (Exception) {
                        return ds;
                    }
                    int _timeElapased = 0;
                    double _InterestAccrued = 0;
                    double _balanceAsOnLastTxDate = 0;
                    string _advancevalue = "";
                    double _Calbalance = 0;
                    double _NoofRepaymentmade = 0;
                    try{
                        advCharges=propObj.Principal+propObj.MonthlyFee+propObj.OtherCharges+propObj.SecurityFee+propObj.BrokerageFee+propObj.CreditSearchFee+propObj.EstabalishmentFee;     
                    }catch(Exception){
                    }
                   // _InterestAccrued = LoanMethod.SimpleInterest(propObj.LoanDate, propObj.FirstRepaymentDate, propObj.InterestRate, advCharges);  
                    if (ds.Tables.Contains("RateChange"))
                    {
                        for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                        {
                            if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Time Lapsed up to last TX Date")
                            {
                                _timeElapased = Convert.ToInt32(ds.Tables["RateChange"].Rows[i]["ChangeFreq"]);
                            }
                            else if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "No. of Repayments Made (M2)")
                            {
                                _NoofRepaymentmade = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeFreq"]);
                            }
                            else if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Balance as on last Tx date.")
                            {
                                try{
                                _balanceAsOnLastTxDate = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeFreq"]);
                                }catch(Exception){}
                            }
                            else if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Last Tx Date is for")
                            {
                                _advancevalue = Convert.ToString(ds.Tables["RateChange"].Rows[i]["ChangeFreq"]);
                            }
                            else if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Interest from Last Tx Date")
                            {
                                try
                                {
                                    _InterestAccrued = Convert.ToDouble(ds.Tables["RateChange"].Rows[i]["ChangeFreq"]);
                                }
                                catch (Exception) { }
                            }
                        }
                    }
                    _Calbalance = _balanceAsOnLastTxDate + _InterestAccrued; 
                    double Repayment = 0;
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        if (ds.Tables["DifferenceTable"].Rows.Count > 0)
                        {
                            Repayment = Convert.ToDouble(ds.Tables["DifferenceTable"].Rows[0]["Repayment"]);
                        }
                    }
                    double _trailrate = 0;
                    _trailrate = LoanMethod.trailRate(propObj.Frequency, propObj.InterestRate);
                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, 0, false, _advancevalue);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", 0);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference);
                    propObj.Principal = _Calbalance;
                    
                    advCharges = _Calbalance;

                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, 0, false, _advancevalue);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", 0);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference);

                    propObj.LoanDuration = propObj.LoanDuration - _timeElapased;

                    int _NoofInstallment = LoanMethod.No_of_Installments(propObj.LoanDuration, propObj.Frequency);
                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _NoofInstallment, false, _advancevalue);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "",_NoofInstallment);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference);

                    if (ds.Tables.Contains("RateChange"))
                    {
                        for (int i = 0; i < ds.Tables["RateChange"].Rows.Count; i++)
                        {
                            if (Convert.ToString(ds.Tables["RateChange"].Rows[i]["Description"]) == "Last Tx date before Effective Date")
                            {
                                propObj.LoanDate = Convert.ToDateTime(ds.Tables["RateChange"].Rows[i]["ChangeFreq"]).ToString("dd/MM/yyyy"); ;
                            }
                        }
                    }

                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Repayment, propObj, advCharges, _trailrate * 100, _NoofInstallment, false, _advancevalue);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, Repayment, propObj.InterestRate, "", _NoofInstallment);
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        ds.Tables.Remove("LoanTable");
                        _dtLoan.TableName = "LoanTable";
                        ds.Tables.Add(_dtLoan);
                    }
                    if (ds.Tables.Contains("DifferenceTable"))
                    {
                        ds.Tables.Remove("DifferenceTable");
                    }
                    if (ds.Tables.Contains("RateChange"))
                    {
                        ds.Tables.Remove("RateChange");
                    }
                    ds.Merge(_dsDifference);
                    propObj.Frequency = propObj.NextFrequency;
                    propObj.FirstRepaymentDate = Convert.ToDateTime(propObj.NextRepaymentDue).ToString("dd/MM/yyyy");
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, false, true, _advancevalue);
                    if (ds.Tables.Contains("Output"))
                    {
                        if (ds.Tables["Output"].Rows.Count > 0)
                        {
                            ds.Tables["Output"].Rows[0][2] = _NoofRepaymentmade;
                        }
                    }
                }
                else {
                    /*do-all*/
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                    /**/
                }

                //ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", _InstallmentRemain, Repayment, false, true, advancevalue, _trailrate, false);
            }
            else if (propObj.LoanType.ToLower() == "changesinglepayment")
            {
                if (propObj.IsUpdate)
                {
                    /*do-all*/
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                    /**/
                    //Copy previous output
                    DataRow _dr;
                    double Pre_Repayment = 0;
                    double Pre_Repayment_for_retaining = 0;
                    int ApplyPayhash=0;
                    int lastchangespayhash = 0;
                    ApplyPayhash = 0;
                    DataTable dtOutput = new DataTable();
                    dtOutput.Columns.Add("blank");
                    dtOutput.Columns.Add("CurrentBalance");
                    dtOutput.Columns.Add("InterestRate");
                    dtOutput.Columns.Add("RepaymentFrequency");
                    dtOutput.Columns.Add("RepaymentAmount");
                    dtOutput.Columns.Add("RepaymentRemaining");
                    Boolean NoChange = false;
                    if (propObj.applypayhash.Length > 0)
                    {
                        lastchangespayhash = propObj.applypayhash[0];
                        NoChange = true;

                    }
                    if (ds.Tables.Contains("LoanTable"))
                    {
                        for (int i = 0; i < ds.Tables["LoanTable"].Rows.Count; i++)
                        {
                            for (int j = 0; j < propObj.SingleRepayment.Length; j++)
                            {
                                if (Convert.ToString(ds.Tables["LoanTable"].Rows[i]["Payhash"]) == Convert.ToString(propObj.applypayhash[j]))
                                {
                                    if (Convert.ToDouble(ds.Tables["LoanTable"].Rows[i]["Payment"]) != Convert.ToDouble(propObj.SingleRepayment[j]))
                                    {
                                        lastchangespayhash = propObj.applypayhash[j];
                                        NoChange = false;
                                    }
                                }
                            }
                        }
                    }
                    double _repayment = 0;
                    if (ds.Tables.Contains("Output"))
                    {
                        if (ds.Tables["Output"].Rows.Count > 0)
                        {
                            _repayment = Convert.ToDouble(ds.Tables["Output"].Rows[0]["RepaymentAmount"]);
                            Pre_Repayment_for_retaining = _repayment;
                        }
                    }
                    double _trailrate = 0;
                    if (ds.Tables.Contains("InterestTable"))
                    {
                        if (ds.Tables["InterestTable"].Rows.Count > 0)
                        {
                            _trailrate = Convert.ToDouble(ds.Tables["InterestTable"].Rows[0]["TrailRate"]);
                        }
                    }
                  
                   double _balance = 0;
                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100, 0, true, "", true, Pre_Repayment, ApplyPayhash,lastchangespayhash);
                    var maxPayhash = (from r in _dtLoan.AsEnumerable()
                                      where LoanMethod.IsNumeric(r.Field<string>("Payhash")) == true
                                      select r.Field<string>("Payhash")).LastOrDefault();
                    int _maxPayhash = Convert.ToInt32(maxPayhash);
                    int _noofinstallment=LoanMethod.No_of_Installments(propObj.LoanDuration,propObj.Frequency);
                    if (_maxPayhash > _noofinstallment)
                    {
                        _noofinstallment = _maxPayhash;
                    }
                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100,_noofinstallment, true, "", true, Pre_Repayment, ApplyPayhash, lastchangespayhash);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, propObj.InterestRate, "dd/MM/yyyy",_noofinstallment, true);
                    if (_dsDifference.Tables.Contains("singlepayment"))
                    {
                        _dr = dtOutput.NewRow();
                        _dr[0] = "With Present Repayment";
                        _dr[1] = "";
                        _dr[2] = "";
                        _dr[3] = "With New Repayment";
                        _dr[4] = "";
                        _dr[5] = "";
                        dtOutput.Rows.Add(_dr);

                        _dr = dtOutput.NewRow();
                        _dr[0] = "Pay#";
                        _dr[1] = "Date";
                        _dr[2] = "Amount";
                        _dr[3] = "Pay#";
                        _dr[4] = "Date";
                        _dr[5] = "Amount";
                        dtOutput.Rows.Add(_dr);
                        for (int i = 1; i < _dsDifference.Tables["singlepayment"].Rows.Count; i++)
                        {

                            _dr = dtOutput.NewRow();
                            _dr[0] = _dsDifference.Tables["singlepayment"].Rows[i]["CurrentBalance"];
                            _dr[1] = _dsDifference.Tables["singlepayment"].Rows[i]["RepaymentFrequency"];
                            _dr[2] = _dsDifference.Tables["singlepayment"].Rows[i]["RepaymentRemaining"];
                            _dr[3] = "";
                            _dr[4] = "";
                            _dr[5] = "";
                            dtOutput.Rows.Add(_dr);
                        }
                    }
                    _noofinstallment = LoanMethod.No_of_Installments(propObj.LoanDuration, propObj.Frequency);
                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100, _noofinstallment, true, "", true, Pre_Repayment, ApplyPayhash, lastchangespayhash);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, propObj.InterestRate, "dd/MM/yyyy", _noofinstallment, true);
                

                    //Calc_EMI
                    double _preFirstInterest = 0;
                    _balance = 0;
                    try
                    {
                        if (Convert.ToDouble(_dsDifference.Tables["DifferenceTable"].Rows[0]["PreInterestRate"]) > 0)
                        {
                            _preFirstInterest = Convert.ToDouble(_dsDifference.Tables["DifferenceTable"].Rows[0]["PreInterestRate"]);
                        }
                    }
                    catch (Exception)
                    {
                        _preFirstInterest = 0;
                    }
                    _repayment = LoanMethod.InitialInstallment(propObj.InterestRate, propObj.Frequency, propObj.LoanDuration, advCharges, _preFirstInterest, propObj.MonthlyFee, 0);
                    _balance = LoanMethod.Balance(_dsDifference.Tables["DifferenceTable"]);

                    _repayment = LoanMethod.Cal_EMI(_repayment, _balance, propObj.InterestRate, advCharges, _trailrate * 100, propObj,0,true,"",true,Pre_Repayment,ApplyPayhash,lastchangespayhash,Pre_Repayment_for_retaining,NoChange);

                    //if (lastchangespayhash == propObj.applypayhash[0])
                    //{
                    //    propObj.SingleRepayment[0] = _repayment;
                    //}
                    ds = new DataSet();
                    double _compoundfactor = 0;
                    _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, Math.Round(_repayment,2), propObj, advCharges, _trailrate * 100,_noofinstallment,true,"",true,Pre_Repayment,ApplyPayhash,lastchangespayhash,Pre_Repayment_for_retaining,NoChange);
                    _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, propObj.InterestRate,"dd/MM/yyyy",0,true);
                    /*Recalculate Comparison rate for reapyment*/
                    double _comparisonRate = 0;
                    _comparisonRate = 0;
                    _compoundfactor = 0;
                    _compoundfactor = LoanMethod.CompundeFactor(propObj.Frequency);
                    _comparisonRate = _trailrate * _compoundfactor;
                    /**/
                    _dtInterest = LoanMethod.Interest_Rate(propObj.InterestRate / 100, _comparisonRate, _trailrate, propObj);
                    if (_dsDifference.Tables.Contains("singlepayment"))
                    {
                        if (_dsDifference.Tables["singlepayment"].Rows.Count > 0)
                        {
                            int j = 2;
                            for (int i = 1; i < _dsDifference.Tables["singlepayment"].Rows.Count; i++)
                            {
                                if (j <= dtOutput.Rows.Count - 1)
                                {
                                    dtOutput.Rows[j]["RepaymentFrequency"] = _dsDifference.Tables["singlepayment"].Rows[i]["CurrentBalance"];
                                    dtOutput.Rows[j]["RepaymentAmount"] = _dsDifference.Tables["singlepayment"].Rows[i]["RepaymentFrequency"];
                                    dtOutput.Rows[j]["RepaymentRemaining"] = _dsDifference.Tables["singlepayment"].Rows[i]["RepaymentRemaining"];
                                    j += 1;
                                }
                                else
                                {
                                    _dr = dtOutput.NewRow();
                                    _dr[0] = "";
                                    _dr[1] = "";
                                    _dr[2] = "";
                                    _dr[3] = _dsDifference.Tables["singlepayment"].Rows[i]["CurrentBalance"];
                                    _dr[4] = _dsDifference.Tables["singlepayment"].Rows[i]["RepaymentFrequency"];
                                    _dr[5] = _dsDifference.Tables["singlepayment"].Rows[i]["RepaymentRemaining"];
                                    dtOutput.Rows.Add(_dr);
                                }
                            }
                        }
                        if (_dsDifference.Tables.Contains("DifferenceTable"))
                        {
                            if (_dsDifference.Tables["DifferenceTable"].Rows.Count > 0)
                            {
                                _dr = dtOutput.NewRow();
                                _dr[0] = "Last repayment date with existing repayment";
                                _dr[1] = "";
                                _dr[2] = "";
                                _dr[3] = "";
                                _dr[4] ="";
                                _dr[5] = _dsDifference.Tables["DifferenceTable"].Rows[0]["LastRepaymentDate"]; 
                                dtOutput.Rows.InsertAt(_dr,0);
                                _dr = dtOutput.NewRow();
                                _dr[0] = "Repayment required to pay within existing tenor";
                                _dr[1] = "";
                                _dr[2] = "";
                                _dr[3] = "";
                                _dr[4] = "";
                                _dr[5] = _dsDifference.Tables["output"].Rows[0]["RepaymentAmount"];
                                dtOutput.Rows.InsertAt(_dr, 1);
                            }
                        }
                        _dsDifference.Tables.Remove("Output");
                        dtOutput.TableName = "Output";
                        _dsDifference.Tables.Add(dtOutput);
                    }
                    _dtLoan = SetOutputFormat_loanTable(_dtLoan, "dd/MM/yyyy");
                    _dtLoan.TableName = "LoanTable";
                    _dtInterest.TableName = "InterestTable";
                    ds.Tables.Add(_dtLoan);
                    ds.Tables.Add(_dtInterest);
                    ds.Merge(_dsDifference);
                    return ds;
                }
                else
                {
                    /*do-all*/
                    ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                    /**/
                }
            }
            else
            {
                /*do-all*/
                ds = do_All(_desiredACR, propObj, advCharges, "dd/MM/yyyy", 0, 0, true, true);
                /**/
            }
            return ds;
        }
        private static DataSet do_All(double _desiredACR,LoanProperties propObj,double advCharges,string DateFormat="",int NoofInstallment=0,double Repayment=0, Boolean isAdvance=true,Boolean isOutput=false,string advancevalue="",double TrailRate=0,Boolean isRepaymentReCalculate=true)
        {
            DataSet ds = new DataSet();
            DataTable _dtLoan = null;
            DataSet _dsDifference = null;
            DataTable _dtInterest = null;
            // do_all
            // Range("q24") = Round(Range("i4") * Range("d6") / 365, 8)
            //Calc_EMI
            //ACR
            //Range("I9") = Range("q23")
            //ACR
               
            double _repayment = LoanMethod.Repayment(_desiredACR, propObj.Frequency, propObj.LoanDuration,advCharges ,NoofInstallment,Repayment);
            double _trailrate =0;
            if (TrailRate == 0)
            {
                _trailrate = LoanMethod.trailRate(propObj.Frequency, propObj.InterestRate);
            }
            else {
                _trailrate = TrailRate;  
            }
            _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100,NoofInstallment,isAdvance,advancevalue);
            _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, propObj.InterestRate,"",NoofInstallment);
            double _preFirstInterest = 0;
            double _balance = 0;
            try
            {
                if (Convert.ToDouble(_dsDifference.Tables["DifferenceTable"].Rows[0]["PreInterestRate"]) > 0)
                {
                    _preFirstInterest = Convert.ToDouble(_dsDifference.Tables["DifferenceTable"].Rows[0]["PreInterestRate"]);
                }
            }
            catch (Exception)
            {
                _preFirstInterest = 0;
            }
            if (isRepaymentReCalculate)
            {
                _repayment = LoanMethod.InitialInstallment(propObj.InterestRate, propObj.Frequency, propObj.LoanDuration, advCharges, _preFirstInterest, propObj.MonthlyFee, NoofInstallment);
            }
            else {
                _repayment = Repayment;  
            }
            _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100,NoofInstallment,isAdvance,advancevalue);
            _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, propObj.InterestRate,"",NoofInstallment);
            _balance = LoanMethod.Balance(_dsDifference.Tables["DifferenceTable"]);

            //Calc_EMI

            _repayment =LoanMethod.Cal_EMI( Math.Round(_repayment,2), _balance, propObj.InterestRate, advCharges, _trailrate * 100, propObj,NoofInstallment,isAdvance,advancevalue);

            //ACR
            if (TrailRate == 0)
            {
                _trailrate = LoanMethod.ACR(propObj.Frequency, propObj.InterestRate / 100, Math.Round(_repayment,2), propObj, advCharges, NoofInstallment, isAdvance,advancevalue);
            }
            else {
                _trailrate = TrailRate; 
            }
            // //Range("I9") = Range("q23")
            double _comparisonRate = 0;
            double _compoundfactor = 0;
            _compoundfactor = LoanMethod.CompundeFactor(propObj.Frequency);
            _comparisonRate = _trailrate * _compoundfactor;
            // _comparisonRate = LoanMethod.ComparisonRate(propObj.Frequency, propObj.InterestRate);

            //Range("I9") = Range("q23")

            if (isRepaymentReCalculate)
            {
                _repayment = LoanMethod.RevisedRepayment(_comparisonRate * 100, propObj.Frequency, propObj.LoanDuration, propObj.Principal, NoofInstallment);
            }
            else {
                _repayment = Repayment;  
            }

            //ACR
            if (TrailRate == 0)
            {
                _trailrate = LoanMethod.ACR(propObj.Frequency, propObj.InterestRate / 100, _repayment, propObj, advCharges, NoofInstallment, isAdvance, advancevalue);
            }
            else {
                _trailrate = TrailRate;  
            }

            _dtLoan = LoanMethod.CalculateLoanTable(propObj.InterestRate, _repayment, propObj, advCharges, _trailrate * 100,NoofInstallment,isAdvance,advancevalue);
            _dsDifference = LoanMethod.Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, propObj.InterestRate, DateFormat, NoofInstallment, isOutput);
            _dtLoan = SetOutputFormat_loanTable(_dtLoan, DateFormat);
            /*Recalculate Comparison rate for reapyment*/
            _comparisonRate = 0;
            _compoundfactor = 0;
            _compoundfactor = LoanMethod.CompundeFactor(propObj.Frequency);
            _comparisonRate = _trailrate * _compoundfactor;
            /**/
            _dtInterest = LoanMethod.Interest_Rate(propObj.InterestRate / 100, _comparisonRate, _trailrate, propObj);
            _dtLoan.TableName = "LoanTable";
            _dtInterest.TableName = "InterestTable";
            ds.Tables.Add(_dtLoan);
            ds.Tables.Add(_dtInterest);
            ds.Merge(_dsDifference);
            return ds;
        }
        public static DataTable SetOutputFormat_loanTable(DataTable dtLaon,string DateFormat)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ExtraDay", typeof(Int64));
            dt.Columns.Add("nextinterest");
            dt.Columns.Add("repaymentdate");
            dt.Columns.Add("Payhash");
            dt.Columns.Add("date");
            dt.Columns.Add("payment", typeof(Double));
            dt.Columns.Add("Interest_before_adv", typeof(Double));
            dt.Columns.Add("advCharges", typeof(Double));
            dt.Columns.Add("balance", typeof(Double));
            dt.Columns.Add("Interest", typeof(Double));
            dt.Columns.Add("discFator", typeof(Double));
            for (int i = 0; i < dtLaon.Rows.Count; i++)
            {
                DataRow _dr = dt.NewRow();
                _dr[0] = dtLaon.Rows[i]["ExtraDay"];
                _dr[1] = LoanMethod.CDate(Convert.ToString(dtLaon.Rows[i]["nextinterest"]),DateFormat);
                _dr[2] = LoanMethod.CDate(Convert.ToString(dtLaon.Rows[i]["repaymentdate"]), DateFormat);
                _dr[3] = dtLaon.Rows[i]["Payhash"];
                _dr[4] = LoanMethod.CDate(Convert.ToString(dtLaon.Rows[i]["date"]), DateFormat);
                _dr[5] = Math.Round(Convert.ToDouble(dtLaon.Rows[i]["payment"]), 2);
                _dr[6] = dtLaon.Rows[i]["Interest_before_adv"];
                _dr[7] = dtLaon.Rows[i]["advCharges"];
                _dr[8] = Math.Round(Convert.ToDouble(dtLaon.Rows[i]["balance"]), 2);
                _dr[9] = dtLaon.Rows[i]["Interest"];
                _dr[10] =Math.Round(Convert.ToDouble(dtLaon.Rows[i]["discFator"]),4);
                dt.Rows.Add(_dr); 
            }
            return dt;
        }
        
    }
}
